﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DevComponents.DotNetBar;
using DevComponents.AdvTree;
using System.IO;
using Excel;

namespace project_assess.prj_view
{
    public partial class prjview_frm : Office2007Form
    {
        DataBase m_db;
        Mfrm mfrm;
        public prjview_frm(Mfrm frm ) 
        {
            InitializeComponent();
            mfrm = frm;
            m_db = frm.m_db;
            dateTimeInput1.Value = new DateTime(DateTime.Now.Year - 2, 1, 1);
            dateTimeInput2.Value = DateTime.Now;
        }


        //查询考核内容
        private void btn_check_Click(object sender, EventArgs e)
        {
            message_frm frm;
            DateTime time1 = dateTimeInput1.Value;
            DateTime time2 = dateTimeInput2.Value;
            TimeSpan sp = time2 - time1;
            if (sp.Days < 0)
            {
                frm = new message_frm("日期错误,应为递增", "错误", icon_img.alert1);
                frm.ShowDialog(this);
                frm.Close();
                return;
            }
            string sql;
            System.Data.DataTable dt;
            sql = "select [assess_work].[项目编号],[项目名称],[项目经理],[assess_work].[次数],[起始时间],[起始比例],[终止时间],[终止比例],[项目部提取额],[班组提取额] " +
                "from [assess_work],[projects] where [起始时间]>='" + time1.ToShortDateString()
                + "' and [终止时间]<='" + time2.ToShortDateString() + "' and [assess_work].[项目编号]=[projects].[项目编号]";
            dt = m_db.GetDataSet(sql).Tables[0];
            mygrid1.set_data(dt);
        }


        //删除数据
        private void delete_btn_Click(object sender, EventArgs e)
        {
            message_frm frm;
            DateTime time1 = dateTimeInput1.Value;
            DateTime time2 = dateTimeInput2.Value;
            TimeSpan sp = time2 - time1;
            if (sp.Days < 0)
            {
                frm = new message_frm("日期错误,应为递增", "错误", icon_img.alert1);
                frm.ShowDialog(this);
                frm.Close();
                return;
            }
            string sql;
            frm = new message_frm("是否删除,一旦删除,\r\n无法恢复", "询问", icon_img.query);
            frm.ShowDialog(this);
            if (frm.yesorno == true)
            {
                sql = "delete from [assess_work] where [起始时间]>='" + time1.ToShortDateString()
                               + "' and [终止时间]<='" + time2.ToShortDateString() + "'";
                m_db.ExecuteSQL(sql);
                frm.Close();
                mfrm.msg("已删除!");
            }
            else
            {
                frm.Close();
                return;
            }

           
            

        }

        private void btn_export_Click(object sender, EventArgs e)
        {
            message_frm frm;
            DateTime time1 = dateTimeInput1.Value;
            DateTime time2 = dateTimeInput2.Value;
            TimeSpan sp = time2 - time1;
            if (sp.Days < 0)
            {
                frm = new message_frm("日期错误,应为递增", "错误", icon_img.alert1);
                frm.ShowDialog(this);
                frm.Close();
                return;
            }
            string path = System.Windows.Forms.Application.StartupPath + @"\报表\temp.xls";
            string xlsname = time1.ToString("yyyy-MM") + "到" + time2.ToString("yyyy-MM") + "工作量考核统计";
            string path1 = System.Windows.Forms.Application.StartupPath + @"\报表\" + xlsname + ".xls";
            
            if (File.Exists(path1))
            {
                try
                {
                    File.Delete(path1);
                }
                catch(Exception a)
                {
                    MessageBox.Show(a.Message);
                    return;
                }
            }
            File.Copy(path, path1);
            string sql;
            System.Data.DataTable dt;
            sql = "select [assess_work].[项目编号] " +
                "from [assess_work] where [起始时间]>='" + time1.ToShortDateString()
                + "' and [终止时间]<='" + time2.ToShortDateString() + "'";
            dt = m_db.GetDataSet(sql).Tables[0];

            sql = "select [项目经理] " +
          "from [assess_work],[projects] where [起始时间]>='" + time1.ToShortDateString()
          + "' and [终止时间]<='" + time2.ToShortDateString() + "' and [assess_work].[项目编号]=[projects].[项目编号]"
          + " group by [项目经理]";
            System.Data.DataTable manager_dt = m_db.GetDataSet(sql).Tables[0];

            Excel.ApplicationClass excel = new Excel.ApplicationClass();
            System.Reflection.Missing miss = System.Reflection.Missing.Value;
            Excel.Workbook book = (Excel.Workbook)excel.Workbooks.Open(path1.ToString(), 
                miss, miss, miss, miss, miss, miss,
                miss, miss, miss, miss, miss, miss, miss, miss);
            excel.Visible = true;
        
            Worksheet sheet = book.Sheets[1] as Worksheet;
            sheet.Name = xlsname;
            Range r = sheet.Cells[2, 1] as Range;
            for (int i = 0; i < manager_dt.Rows.Count; i++)
            {
                sheet.Copy(sheet, miss);
            }
            for (int i = 1; i < book.Sheets.Count; i++)
            {
                (book.Sheets[i] as Worksheet).Name = manager_dt.Rows[i - 1]["项目经理"].ToString();
            }
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                System.Data.DataTable tempdt;
                string id = dt.Rows[i][0].ToString();
                sql = "select [项目名称],[状态],[合同额],[项目经理],[项目负责人],[班组长]," +
                    "[材料埋设],[工作量] from [projects] where [项目编号]='" + id + "'";
                tempdt = m_db.GetDataSet(sql).Tables[0];
                r.Value2 = id;
                r.get_Offset(0, 1).Value2 = tempdt.Rows[0]["项目名称"];
                r.get_Offset(0, 2).Value2 = tempdt.Rows[0]["状态"];
                r.get_Offset(0, 3).Value2 = tempdt.Rows[0]["合同额"];
                r.get_Offset(0, 4).Value2 = tempdt.Rows[0]["项目经理"];
                r.get_Offset(0, 5).Value2 = tempdt.Rows[0]["项目负责人"];
                r.get_Offset(0, 6).Value2 = tempdt.Rows[0]["班组长"];
                r.get_Offset(0, 7).Value2 = tempdt.Rows[0]["材料埋设"];
                r.get_Offset(0, 8).Value2 = tempdt.Rows[0]["工作量"];
                double work = (double) r.get_Offset(0, 8).Value2;
                r.get_Offset(0, 9).Value2 = (double)r.get_Offset(0, 7).Value2 + (double)r.get_Offset(0, 8).Value2;

                sql = "select [次数],[起始时间],[起始比例],[终止时间],[终止比例],[项目部提取额],[班组提取额] " +
              "from [assess_work] where [起始时间]>='" + time1.ToShortDateString()
              + "' and [终止时间]<='" + time2.ToShortDateString() + "' and [项目编号]='" + id + "'";
                tempdt = m_db.GetDataSet(sql).Tables[0];
                r.get_Offset(0, 10).Value2 = (double)tempdt.Rows[0]["起始比例"]/100;
                r.get_Offset(0, 11).Value2 = work * (1 - (double)r.get_Offset(0, 10).Value2);
                r.get_Offset(0, 12).Value2 = work * mfrm.get_para("工作量项目额度");
                r.get_Offset(0, 13).Value2 = work * mfrm.get_para("工作量班组额度");
                r.get_Offset(0, 14).Value2 = (double)tempdt.Rows[0]["终止比例"]/100;
                double step= ((double)r.get_Offset(0, 14).Value2
                    - (double)r.get_Offset(0, 10).Value2);
                r.get_Offset(0, 15).Value2 = (double)r.get_Offset(0, 3).Value2 * step;
                r.get_Offset(0, 16).Value2 = work * (1 - (double)r.get_Offset(0, 14).Value2);
                r.get_Offset(0, 17).Value2 = work * step;
                r.get_Offset(0, 18).Value2 = tempdt.Rows[0]["项目部提取额"];
                r.get_Offset(0, 19).Value2 = tempdt.Rows[0]["班组提取额"];
                r.get_Offset(0, 20).Value2 = (double)r.get_Offset(0, 17).Value2 * 0.25;
                Range rowrange = sheet.Rows[r.Row, miss] as Range;
                rowrange.Copy(get_range(r.get_Offset(0, 4).Value2.ToString(), book));
                r = r.get_Offset(1, 0);
            }
            r = sheet.get_Range(sheet.Cells[2, 1], sheet.Cells[1 + dt.Rows.Count, 21]);
            r.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
            add_line(book);

        }

        Range get_range(string manager,Workbook book)
        {
            Worksheet sheet = book.Sheets[manager] as Worksheet;
            Range r = sheet.Cells[2, 1] as Range;
            while (r.Value2 != null && r.Value2.ToString()!="")
            {
                r = r.get_Offset(1, 0);
            }
            System.Reflection.Missing miss = System.Reflection.Missing.Value;
            r = sheet.Rows[r.Row, miss] as Range;
            return r;
        }

        void add_line(Workbook book)
        {
            for (int i = 1; i < book.Sheets.Count; i++)
            {
                Worksheet sheet = book.Sheets[i] as Worksheet;
                Range r = sheet.Cells[2, 1] as Range;
                while (r.Value2 != null && r.Value2.ToString() != "")
                {
                    r = r.get_Offset(1, 0);
                }
                r = sheet.get_Range(sheet.Cells[2, 1], sheet.Cells[r.Row - 1, 21]);
                r.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
            }
            
        }


    }
}
